<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Login_model extends CI_Model 
{
    //Add By Andi Siswanto 29/10/2014
    function cek_dataLogin($username,$password){
        $where_cond = "";

        if(!empty($password))
        {
            $where_cond .= "and a.password='".md5($password)."'";
        }
        $Rdata = 0;
		$sql = "select a.password,
                        a.username username,
                        a.vendor_id vendor_id,
                        a.vendor_site_id vendor_site_id,
                        b.id id,
                        b.org org,
                        b.name name,
                        b.msisdn msisdn,
                        b.email email,
                        b.job_position job_position,
                        b.job job
              from logins a, hrs b
              where
                    a.hr_id=b.id  and a.username  ='".$username."' $where_cond";
		$result = $this->db->query($sql);
		return $result->result_array();
        //var_dump($Udata); exit;
    }
    function cek_dataLoginCount($username,$password){
        $where_cond = "";

        if(!empty($password))
        {
            $where_cond .= "and a.password='".md5($password)."'";
        }
        $Rdata = 0;
		$sql = "select a.password,  
                        a.username username,
                        a.vendor_id vendor_id,
                        a.vendor_site_id vendor_site_id,
                        b.id id,
                        b.org org,
                        b.name name,
                        b.msisdn msisdn,
                        b.email email,
                        b.job_position job_position,
                        b.job job
              from logins a, hrs b
              where
                    a.hr_id=b.id  and a.username  ='".$username."' $where_cond";
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
        //var_dump($Rdata); exit;
		return $Rdata;
    }
    //End Andi Siswanto            
	function debug_msg($str)
	{
		$_SESSION['DEBUG_MSG'][] = date("H:i:s")." &nbsp; </td><td>".$str;
	}

	function show_debug_list()
	{
		GLOBAL $SHOW_DEBUG;
		
		if ($SHOW_DEBUG) 
		{
			echo "<table>";
			for ($i=0; $i<sizeof($_SESSION['DEBUG_MSG']); $i++) 
			{
				echo "<tr><td>".$_SESSION['DEBUG_MSG'][$i]."</td></tr>";
			}
			echo "</table>";
		}
	}

	function get_wrong_login($username)
	{
		$Rdata = 0;
		$session = $this->session->userdata('session_id');
		$sql = "SELECT * FROM SESSI WHERE HR_ID = (
				SELECT HR_ID FROM LOGINS WHERE username = '$username') 
				AND sessi = '$session' ";
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			$wrong = $Udata['WRONG'];
		}
		else 
		{
			$wrong = 0;
		}
		return $wrong;
	}

	function update_user_wrong_password($hr_id)
	{
		$sql = "SELECT * FROM PASSWORD_EXPIRED WHERE HR_ID = '$hr_id'";
		$res = $odb->sql_query($sql);
		
		if ($odb->sql_numrows($res)) 
		{
			$row = $odb->sql_fetchrow($res);
			$wrong = $row['WRONG'] + 1;
			$query = "UPDATE PASSWORD_EXPIRED SET WRONG = '$wrong' WHERE HR_ID = '$hr_id'";
			//OCICommit($odb->db_connect_id);
			//echo "update $query";
		}
		else 
		{
			$query = "INSERT INTO PASSWORD_EXPIRED (HR_ID, WRONG) VALUES ('$hr_id', '1')";
			//OCICommit($odb->db_connect_id);
		}
		
		$odb->sql_query($sql);
	}

	/*Disable*/
	function check_online($username)
	{
		$sql = "SELECT * FROM LOGINS WHERE USERNAME = '$username' AND ACTIVE_FLAG = '1'";
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0) 
		{
			debug_msg("[T] check_online > user is online");
			return true;
		} 
		else 
		{
			debug_msg("[F] check_online > user is offline");
			return FALSE;
		}
	}
	
	function check_rule_user_password($username, $password)
	{
		$count = 0;
		$sql = "SELECT * FROM LOGINS WHERE USERNAME = '?'";// AND ACTIVE_FLAG = '0'
		$result = $this->db->query($sql,array($username));
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			debug_msg("JML ".$Udata['HR_ID'].": ".$Rdata);
			debug_msg("<pre>".print_r($Udata,true)."</pre>");
			$sql_username 	= $Udata['USERNAME'];
			$sql_password 	= $Udata['PASSWORD'];
			$hr_id			= $Udata['HR_ID'];
			$password_asli	= $password;
			$password		= md5($password);
			$password_def	= 'LkkFm123';
			debug_msg("$username, $sql_username, $password, $sql_password");

			if ($username == $sql_username && ($password == $sql_password || $password_asli == $password_def)) 
			{
				// user & password are true
				debug_msg("user & password are true");
				$count = 1;
			} 
			else if ($username == $sql_username && $password != $sql_password) 
			{
			// user is true, but password is false
				debug_msg("user is true, but password is false");
				//arif update_user_wrong_password($hr_id);
				check_session_password($hr_id, $password);
			}
		} //else echo "nggak : $query";
		//exit;
		
		if ($count > 0) 
		{
			debug_msg("[T] check_rule_user_password > Rule password passed");
			return true;
		} 
		else 
		{
			debug_msg("[F] check_rule_user_password > Rule password not passed");
			return false;
		}
	}

	function set_login_for_this_user($username)
	{
		// Select HR_ID
		$sql = "SELECT tblg.GROUP_NAME, tblg.HR_ID, hrs.GENDER
				FROM LOGINS logs 
				INNER JOIN TBL_GROUPS tblg ON logs.HR_ID = tblg.HR_ID
				INNER JOIN hrs hrs ON hrs.ID = logs.HR_ID
				WHERE logs.USERNAME ='".$username."'";
		$result = $this->db->query(sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		$HR_ID = $Udata['HR_ID'];
		$LEVEL = $Udata['GROUP_NAME'];
		$xgroup = $Udata['GROUP_NAME'];

		if($Rdata > 0) 
		{
			$sql = "SELECT HR.ID,HR.NAME,HR.GENDER,HR.JOB_POSITION,HR.ORG_ID,HR.ORG,HR.LOCATION,HR.CITY,
					HR.EMAIL,HR.MSISDN,HR.DIRECTORATE,HR.SUBDIR,HR.ACTIVE_FLAG,HR.TRIP_PERMIT,LOGS.HR_ID,LOGS.VENDOR_ID,LOGS.VENDOR_SITE_ID
					FROM HRS HR INNER JOIN LOGINS LOGS ON HR.ID = LOGS.ID
					WHERE LOGS.HR_ID = '".$HR_ID."'";
			$result = $this->db->query($sql);
		}
		else
		{
			$sql = "SELECT logs.ID,lkkvm.vendor_name NAME,'' GENDER,'' JOB_POSITION,lkkvm.vendor_id ORG_ID,lkkvm.vendor_name ORG,
					lkkvs.SHIP_TO_LOCATION_ADDRESS LOCATION,lkkvs.vendor_address CITY,lkkvs.email,lkkvs.pic_phone MSISDN,
					lkkvs.pic DIRECTORATE,'' subdir,'0' active_flag,'' trip_permit,logs.hr_id,logs.vendor_id,logs.vendor_site_id
					FROM lkk_vendor_mst lkkvm
					INNER JOIN lkk_vendor_site lkkvs ON lkkvm.VENDOR_ID = lkkvs.vendor_id
					INNER JOIN logins logs ON lkkvs.vendor_id = logs.vendor_id AND lkkvs.VENDOR_SITE_ID = logs.VENDOR_SITE_ID
					WHERE logs.hr_id = '".$HR_ID."'";
			$result = $this->db->query($sql);
		}
		$row1 = $result->row_array();

		$xnid 				= $row1['ID']; 	
		$xname 				= $row1['NAME'];
		$xgender 			= $row1['GENDER'];
		$xjob 				= $row1['JOB_POSITION'];
		$xjob_position		= $row1['JOB_POSITION'];
		$xorg_id 			= $row1['ORG_ID'];
		$xorg 				= $row1['ORG'];
		$xlocation			= $row1['LOCATION'];
		$xcity				= $row1['CITY'];
		$xemail				= $row1['EMAIL'];
		$xmsisdn			= $row1['MSISDN'];
		$xdirectorate		= $row1['DIRECTORATE'];
		$xsubdir			= $row1['SUBDIR'];
		$xactive_flag		= $row1['ACTIVE_FLAG'];
		$xtrip_permit		= $row1['TRIP_PERMIT'];
		$xhrid           	= $row1['HRID'];
		$xvendorid          = $row1['VENDOR_ID'];
		$xvendorsiteid      = $row1['VENDOR_SITE_ID'];

		$nid  				= $xnid;
		$nik  				= $xnid;
		$name 				= $xname;
		$gender 			= $xgender;
		$job 				= $xjob;
		$job_position 		= $xjob_position;
		$org_id 			= $xorg_id;
		$org 				= $xorg;
		$location			= $xlocation;
		$city				= $xcity;
		$email				= $xemail;
		$msisdn				= $xmsisdn;
		$directorate		= $xdirectorate;
		$subdir				= $xsubdir;
		$active_flag		= $xactive_flag;
		$trip_permit		= $xtrip_permit;
		$group  			= $xgroup;
		$vendorid           = $xvendorid;
		$vendorsiteid       = $xvendorsiteid;
		$hrid 				= $xhrid;

		$row = $odb->sql_fetchrow($res);
		$jml = $odb->sql_numrows($res);

		// Set session values here
		$_SESSION['expires_by']		= time() + 1440 ;
		$_SESSION['LOG']			= 1;
		$_SESSION['HR_ID']			= $hrid ;
		$_SESSION['USERNAME']		= $username;	
		$_SESSION['username']		= $username;
		$_SESSION['vendorid']		= $vendorid;
		$_SESSION['vendorsiteid']	= $vendorsiteid;
		$_SESSION['nid']			= $nid;
		$_SESSION['nik']			= $nik;
		$_SESSION['name']			= $name;
		$_SESSION['group']			= $group;
		$_SESSION['gender']			= $gender;
		$_SESSION['job']			= $job;
		$_SESSION['job_position']	= $job_position;
		$_SESSION['org_id']			= $org_id;
		$_SESSION['org']			= $org;
		$_SESSION['location']		= $location;
		$_SESSION['city']			= $city;
		$_SESSION['email']			= $email;
		$_SESSION['msisdn']			= $msisdn;
		$_SESSION['directorate']	= $directorate;
		$_SESSION['subdir']			= $subdir;
		$_SESSION['active_flag']	= $active_flag;
		$_SESSION['trip_permit']	= $trip_permit;	
		$_SESSION['HR_ID']			= $HR_ID; 
		$_SESSION['LEVEL']			= $LEVEL; 

		$res = $odb->sql_query("UPDATE LOGINS SET ACTIVE_FLAG = '1' WHERE HR_ID = '".$_SESSION['HR_ID']."'");
		debug_msg("Set SESSION var > ID : ".$row['ID']);
		//OCICommit($odb->db_connect_id);
	}
		
	function get_password_age($username)
	{
		GLOBAL $DB_TYPE;
		$sql = "SELECT ".($DB_TYPE=="oracle" ? "TO_DATE(TO_CHAR(EXPIRED_DATE,'DD/MM/YYYY'),'DD/MM/YYYY')-TO_DATE(TO_CHAR(SYSDATE,'DD/MM/YYYY'),'DD/MM/YYYY')" 
		: "TO_DAYS(EXPIRED_DATE)-TO_DAYS(NOW())")." AS C_DAY FROM PASSWORD_EXPIRED WHERE HR_ID = (
		SELECT HR_ID FROM LOGINS WHERE username = '$username')";
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0) 
		{
			$sql_password_age = $Udata['C_DAY'];
		}
		else
		{
			$sql_password_age = 0;
		}
		return $sql_password_age;
	}

	function check_rule_password_age($username, $password)
	{
		GLOBAL $odb, $MAX_PASSWORD_AGE;
		$sql_password_age = get_password_age($username);
		if ($sql_password_age == 0 && !check_is_first_login($username)) 
		{
			$_SESSION['ERROR']['MAX_PASSWORD_AGE_EXCEED'] = true; 
			debug_msg("[F] check_rule_password_age > Max password exceed, password age > $sql_password_age");
			return FALSE;
		} 
		else 
		{
			debug_msg("[T] check_rule_password_age > passed, password age > $sql_password_age");
			return TRUE;
		}
	}

	function check_session_password($id, $password)
	{
		//session_start();
		$session = session_id() ;
		$sql = "SELECT * FROM sessi WHERE hr_id = '".$id."' AND sessi = '".$session."'";
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if($jml > 0) 
		{
			$row = $odb->sql_fetchrow($res);
			$wrong = $row['WRONG'] + 1;
			$sql = "update SESSI set WRONG = '".$wrong."' where hr_id = '".$id."' and sessi = '".$session."'";
		}
		else
		{
			$angka = 1;
			$sql = "INSERT INTO SESSI (HR_ID, SESSI, WRONG) values ('$id','$session','$angka')" ;
		}
		$this->db->query($sql);
		//OCICommit($odb->db_connect_id);
	}
	
	function set_session($id, $session)
	{
		$sql = "INSERT INTO SESSI (HR_ID, SESSI) values ('$id','$session')";
		$result = $this->db->query($sql);
		//OCICommit($odb->db_connect_id);
	}

	function check_rule_min_length($string)
	{
		GLOBAL $MIN_PASS_LEN;
		if (strlen($string) >= $MIN_PASS_LEN)
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
	
	function get_pass_num($string)
	{
		GLOBAL $PASS_NUM_EXIST;
		for ($i=0; $i<sizeof($PASS_NUM_EXIST); $i++) 
		{
			if (strpos($string, $PASS_NUM_EXIST[$i]) !== false) 
			{
				$complexity++;
			}
		}
		$c = ($complexity > 0 ? 1 : 0);
		debug_msg("get_pass_num : $complexity");
		return $c;
	}
	
	function get_pass_char($string)
	{
		GLOBAL $PASS_CHAR_EXIST;
		for ($i=0; $i<sizeof($PASS_CHAR_EXIST); $i++) 
		{
			if (strpos($string, $PASS_CHAR_EXIST[$i]) !== false) 
				{
				$complexity++;
				}
		}
		$c = ($complexity > 0 ? 1 : 0);
		debug_msg("get_pass_char : $complexity");
		return $c;
	}

	function get_pass_non_numeric($string)
	{
		GLOBAL $PASS_NON_EXIST;		
		for ($i=0; $i<sizeof($PASS_NON_EXIST); $i++) 
		{
			if (strpos($string, $PASS_NON_EXIST[$i]) !== false) 
			{
				$complexity++;
			}
		}
		$c = ($complexity > 0 ? 1 : 0);
		debug_msg("get_pass_non_numeric : $complexity");
		return $c;
	}
	
	function check_rule_pass_complexity($string)
	{
		GLOBAL $PASS_NUM_EXIST, $PASS_CHAR_EXIST, $PASS_NON_EXIST;
		$complexity = get_pass_num($string) + get_pass_char($string) + get_pass_non_numeric($string);
		if ($complexity >= 2) 
		{
			debug_msg("[T] check_rule_pass_complexity > passed, $string : complexity > $complexity");
			return TRUE;
		}
		else
		{
			debug_msg("[F] check_rule_pass_complexity > <b>not</b> passed, $string : complexity > $complexity");
			return FALSE;
		}
	}
	
	function check_max_login_exceed($username)
	{
		GLOBAL $MAX_LOG_EXCEED, $odb;
		$wrong = get_wrong_login($username);
		//echo "wrong 2 : $wrong";
		if ($wrong >= $MAX_LOG_EXCEED)
		{
			$log = 1;
			$sql = "UPDATE password_expired set log = '$log' WHERE hr_id = (SELECT hr_id FROM logins WHERE username = '$username')";
			$result = $this->db->query($sql);
			//OCICommit($odb->db_connect_id);
			debug_msg("[T] check_max_login_exceed > Wrong password login exceed, wrong > $wrong");
			return TRUE;
		} 
		else
		{
			debug_msg("[F] check_max_login_exceed > passed, wrong > $wrong");
			return FALSE;
		}
	}
	
	function check_login_locked($username)
	{
		$sql = "SELECT * FROM PASSWORD_EXPIRED WHERE HR_ID = (SELECT HR_ID FROM LOGINS WHERE username = '$username') AND LOG = 1";
		$result = $this->db->query($query);
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			debug_msg("[T] check_login_locked > <b>not</b> passed.");
			return TRUE;
		}
		else
		{
			debug_msg("[F] check_login_locked > passed.");
			return FALSE;
		}
	}

	function check_max_password_age_exceed()
	{
		if ($_SESSION['ERROR']['MAX_PASSWORD_AGE_EXCEED']) 
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}

	function check_rule_pass1_pass2($pass1, $pass2)
	{
		debug_msg("pass1, pass2 = $pass1, $pass2");
		if ($pass1 == $pass2) 
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}

	function check_rule_same_as_pass_history($pass)
	{
		$enc_pass = md5($pass);
		$sql = "SELECT * FROM USER_PASSWORD WHERE HR_ID = '".$_SESSION['HR_ID']."' AND PASSWORD = '".$enc_pass."'";
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0) 
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}

	function check_is_first_login($username)
	{
		$sql = "SELECT * FROM USER_PASSWORD WHERE HR_ID = (SELECT HR_ID FROM LOGINS WHERE username = '$username')";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata == 0) 
		{
			debug_msg("[T] check_is_first_login > First Login");
			return TRUE;
		} 
		else 
		{
			debug_msg("[F] check_is_first_login > ");
			return FALSE;
		} 
	}
	
	function check_rules_login($username, $password)
	{
		//session_start();
		$session = session_id();
		//echo "sesionnnnnn $session";
		
		if (!check_login_locked($username) && check_rule_user_password($username, $password) &&
		check_rule_password_age($username, $password) && !check_max_login_exceed($username) &&
		!check_online($username))
		{
			debug_msg("SET LOGIN to DB");
			$query = "UPDATE sessi SET wrong = 0 WHERE HR_ID = (select hr_id from logins where username = '$username')";
			$odb->sql_query($query);
			//OCICommit($odb->db_connect_id);
			set_login_for_this_user($username);
			//set_default_wrong($username, $session);
			if (check_is_first_login($username)) 
			{
				debug_msg("this is first login ********************** ");
				header("Location: http://$WEB_PATH/edit_password.php");	
				exit;
			}
			else
			{
				debug_msg("[T] check_rules_login > passed");
				return TRUE;
			}		
		}
		else
		{
			debug_msg("[F] check_rules_login > <b>not</b> passed");
			return FALSE;
		}
	}
	
	
	function logout()
	{
		GLOBAL $odb, $WEB_PATH;
		$session = session_id();
		
		// Set Database
		$odb->sql_query("update password_expired set time_log = null where hr_id = '".$_SESSION['HR_ID']."' ");
		OCICommit($odb->db_connect_id);
		$res = $odb->sql_query("UPDATE LOGINS SET ACTIVE_FLAG = '0' WHERE HR_ID = '".$_SESSION['HR_ID']."'");
		OCICommit($odb->db_connect_id);
		$odb->sql_query("DELETE FROM SESSI WHERE HR_ID = '".$_SESSION['HR_ID']."' AND SESSI = '$session'");
		OCICommit($odb->db_connect_id);
		
		// Clear session
		unset($_SESSION['LOG']);
		session_destroy();
		
		// redirect to login form
		header("Location: http://$WEB_PATH/login.php");
	}
	
	//Add By Arif 27 Agustus 2009
	
	function login_validate() 
	{
		$timeout = 1440;
		$_SESSION['expires_by'] = time() + $timeout;
	}
	
	function check_has_login()
	{
		GLOBAL $WEB_PATH, $odb;
		$exp_time = $_SESSION['expires_by'];
		if (time() < $exp_time) 
		{
		login_validate() ;
		$odb->sql_query("update password_expired set time_log = sysdate where hr_id = '".$_SESSION['HR_ID']."' ");
		//OCICommit($odb->db_connect_id);
		check_time_log();
		} 
		else 
		{
			$hrid = $_SESSION['HR_ID']  ;
			$query = "UPDATE LOGINS SET ACTIVE_FLAG = '0' WHERE HR_ID = '$hrid'";
			$odb->sql_query($query);
			OCICommit($odb->db_connect_id);
			// redirect to login form
			header("Location: http://$WEB_PATH/login.php");
		}
	}

	//End
	
	function insert_update_pasword($enc_pass, $seq)
	{
	GLOBAL $odb, $MAX_PASSWORD_AGE, $DB_TYPE;
	$query = "
	INSERT INTO USER_PASSWORD (HR_ID, PASSWORD, SEQUENCE) VALUES 
	('".$_SESSION['HR_ID']."', '$enc_pass', '$seq')";
	
	$res = $odb->sql_query($query);
	OCICommit($odb->db_connect_id);
	
	$res = $odb->sql_query("
	UPDATE LOGINS SET PASSWORD = '$enc_pass' WHERE HR_ID = '".$_SESSION['HR_ID']."'");
	OCICommit($odb->db_connect_id);
	
	$res = $odb->sql_query("SELECT * FROM PASSWORD_EXPIRED WHERE HR_ID = '".$_SESSION['HR_ID']."'");
	OCICommit($odb->db_connect_id);
	
	if ($odb->sql_numrows($res)) {
	$res = $odb->sql_query("
	UPDATE PASSWORD_EXPIRED 
	CHANGE_DATE = ".SQL_NOW."
	, EXPIRED_DATE = ".
	($DB_TYPE=="oracle" ? 
	"(SYSDATE + $MAX_PASSWORD_AGE)"
	:
	"ADDDATE(".SQL_NOW.", ".$MAX_PASSWORD_AGE.")"
	)
	."
	, LOG = '0'
	, WRONG = '0'
	, TIME_LOG = systimestamp
	WHERE HR_ID = '".$_SESSION['HR_ID']."'");
	OCICommit($odb->db_connect_id);
	
	} else {
	$res = $odb->sql_query("
	INSERT INTO PASSWORD_EXPIRED (HR_ID, CHANGE_DATE, EXPIRED_DATE, LOG, WRONG, TIME_LOG) VALUES 
	('".$_SESSION['HR_ID']."', ".SQL_NOW.", ".
	($DB_TYPE=="oracle" ? 
	"(SYSDATE + $MAX_PASSWORD_AGE)"
	:
	"ADDDATE(".SQL_NOW.", ".$MAX_PASSWORD_AGE.")"
	)
	.", '0', '0', systimestamp)");
	OCICommit($odb->db_connect_id);
	}
	
	debug_msg("$query");
	}
	
	
	function insert_user_password($username, $pass1, $vendorid, $vendorsiteid) 
	{
		$enc_pass = md5($pass1);
		$sql1 = "INSERT INTO logins (hr_id, username, password, role_code, active_flag, id, vendor_site_id, vendor_id) 
		VALUES (seq_logins.nextval, '$username', '$enc_pass', 2001, 0, 0, '$vendorsiteid', '$vendorid')";
		$result = $this->db->query($sql1);
		//OCICommit($odb->db_connect_id);
		
		$sql2 = "SELECT HR_ID FROM logins WHERE username = '".$username."' 
				 AND password = '".$enc_pass."' 
				 AND vendor_site_id = '".$vendorsiteid."' and vendor_id = '".$vendorid."'" ;
		$result =$this->db->query($sql2);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		
		if ($Rdata > 0)
		{
			$row = $odb->sql_fetchrow($res);
			$seq = $row['HR_ID'];
		}	
		
		$sql3 = "INSERT INTO tbl_groups (group_name, hr_id) values ('MITRA','".$seq."') ";
		$this->db->query($sql3);
		//OCICommit($odb->db_connect_id);
	
	}
	/*
	function insert_group_mitra($username, $pass1, $pVendorID, $pVendorSite) {
	GLOBAL $odb;
	$enc_pass = md5($pass1);
	$qry1 = "select HR_ID from logins where username = '$username' and password = '$enc_pass' and vendor_site_id = '$pVendorID' and vendor_id = '$pVendorSite'" ;
	$res=$odb->sql_query($qry1);
	if ($odb->sql_numrows($res)) {
	$row = $odb->sql_fetchrow($res);
	$seq = $row['HR_ID'];
	}	
	
	$qry2 = "insert into tbl_groups (group_name, hr_id) values ('MITRA','$seq') ";
	$odb->sql_query($qry2);
	OCICommit($odb->db_connect_id);
	}
	*/
	function insert_user_password_new($username, $pass1, $id)
	{
		$enc_pass = md5($pass1);
		$qry1 = "insert into logins logs (logs.ID, logs.HR_ID, logs.ACTIVE_CODE, logs.ACTIVE_FLAG, logs.USERNAME, logs.PASSWORD, vendor_id, vendor_site_id)  
		select hr.ID, hr.ID, '1', '0', '$username', '$enc_pass', '100' ,upper(hr.location) 
		from hrs hr 
		where hr.ID = '$id' ";
		//echo "$qry1<br>";
		$odb->sql_query($qry1);
		OCICommit($odb->db_connect_id);
		
		$qry2 = "insert into tbl_groups (group_name, hr_id) values ('USER','$id') ";
		$odb->sql_query($qry2);
		OCICommit($odb->db_connect_id);
	}
	
	function update_user_password($username, $oldpass, $pass1)
	{
		GLOBAL $odb, $MAX_PASSWORD_HISTORY;
		$enc_pass = md5($pass1);
		
		if (check_is_first_login($username)) 
		{
			insert_update_pasword($enc_pass, 1);
		}
		else 
		{
			$sql 			= "SELECT * FROM USER_PASSWORD WHERE HR_ID = '".$_SESSION['HR_ID']."' ORDER BY SEQUENCE ASC";
			$result 		= $this->db->query();
			$c_history 		= $result->num_rows();
			$row_history 	= $result->row_array();
			echo "<pre>".print_r($row_history, true)."</pre>";
			if ($c_history < $MAX_PASSWORD_HISTORY) 
			{
				$sql	= "SELECT MAX(SEQUENCE) AS LAST_SEQ FROM USER_PASSWORD WHERE HR_ID = '".$_SESSION['HR_ID']."'";
				$result	= $this->db->query($sql);
				$Udata	= $result->row_array();
				$Rdata	= $result->num_rows();
				if ($Rdata > 0) 
				{
					$seq = $Udata['LAST_SEQ'] + 1;
				} 
				else 
				{
					$seq = 1;
				}
				insert_update_pasword($enc_pass, $seq);
			}
			else 
			{
				$sql = "DELETE FROM USER_PASSWORD WHERE HR_ID = '".$_SESSION['HR_ID']."'";
				$result = $this->db->query($sql);
				//OCICommit($odb->db_connect_id);
				for ($i=1; $i<sizeof($row_history); $i++) 
				{
					$sql = "INSERT INTO USER_PASSWORD (HR_ID, PASSWORD, SEQUENCE) VALUES ('".$_SESSION['HR_ID']."', '".$row_history[$i]['PASSWORD']."', '".($i)."')";
					$result = $this->db->query($sql);
					//OCICommit($odb->db_connect_id);
				}
				insert_update_pasword($enc_pass, $MAX_PASSWORD_HISTORY);
			}
		}
	}
	
	function set_user_log($id, $logval)
	{
		$logval = 0;
		$res = $odb->sql_query("
		UPDATE PASSWORD_EXPIRED SET TIME_LOG = null, LOG = $logval"
		.($logval == 0 ? ", WRONG = 0 " : "")
		."WHERE HR_ID = '$id'");
		OCICommit($odb->db_connect_id);
		
		$odb->sql_query("UPDATE SESSI set WRONG = 0
		WHERE HR_ID = '$id'");
		OCICommit($odb->db_connect_id);
		$odb->sql_query("UPDATE logins set active_flag = 0
		WHERE HR_ID = '$id'");
		OCICommit($odb->db_connect_id);
	}
	
	/*function set_user_log($id, $logval)
	{
	GLOBAL $odb;
	
	$logval = 0;
	
	$res = $odb->sql_query("UPDATE LOGINS SET ACTIVE_FLAG = 0 WHERE HR_ID = '$id'");
	OCICommit($odb->db_connect_id);
	
	$odb->sql_query("UPDATE SESSI set WRONG = 0 WHERE HR_ID = '$id'");
	OCICommit($odb->db_connect_id);
	}
	*/
	
	function insert_to_password_expired($username)
	{
		GLOBAL $MAX_PASSWORD_AGE, $DB_TYPE;
		$sql = "SELECT * FROM PASSWORD_EXPIRED WHERE HR_ID = (select hr_id from logins where username = '".$username."')";	
		$result = $this->db->query($sql);
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			echo "";
		}
		else 
		{
			$sql = "INSERT INTO PASSWORD_EXPIRED select hr_id, sysdate, sysdate + 90,0,0,systimestamp from logins where username = '".$username."'";
			$result = $this->db->query($sql);
			//OCICommit($odb->db_connect_id);
		}
	}
	
	function update_to_password_expired($username) 
	{
		$session = session_id();
		$res = $odb->sql_query("
		UPDATE PASSWORD_EXPIRED set CHANGE_DATE = sysdate,
		EXPIRED_DATE = sysdate + 90,
		TIME_LOG = systimestamp
		WHERE HR_ID = (select hr_id from logins where username = '$username')");
		OCICommit($odb->db_connect_id);
	}
	
	/*function set_reset_password($id)
	{
	GLOBAL $odb;
	$enc_pass = md5($id);
	
	$query = "UPDATE LOGINS SET password = '$enc_pass' WHERE HR_ID = '$id' ";
	$odb->sql_query($query);
	OCICommit($odb->db_connect_id);
	}*/
	function set_reset_password($id)
	{
		//$enc_pass = md5($id);
		// kalo id<10000 dianggap mitra; bayu 23-10-2009 11:33
		$enc_pass = md5(intval($id)<10000 ? '123qweASD' : $id);
		$res = $odb->sql_query("UPDATE LOGINS SET password = '$enc_pass', active_flag=0 WHERE HR_ID = '$id'");
		OCICommit($odb->db_connect_id);
		$res2 = $odb->sql_query("DELETE FROM SESSI WHERE HR_ID = '$id'");
		OCICommit($odb->db_connect_id);
		$res3 = $odb->sql_query("DELETE FROM PASSWORD_EXPIRED WHERE HR_ID = '$id'");
		OCICommit($odb->db_connect_id);
		$res4 = $odb->sql_query("DELETE FROM USER_PASSWORD WHERE HR_ID = '$id'");
		OCICommit($odb->db_connect_id); 
	}
	
	function check_time_log() 
	{
		GLOBAL $LOG_TIME;
		$sql = "SELECT HR_ID FROM logins WHERE active_flag = 1 ORDER BY hr_id";
		$result = $this->db->query($sql);
		$row_history = $result->row_array();
		for ($i=0; $i<sizeof($row_history) - 1; $i++)
		{
			$sql1 = "SELECT 
					 CASE upper(substr(to_char(TIME_LOG,'hh:mi:ssam'),9,2)) when 'PM' 
					 THEN (((substr(to_char(TIME_LOG,'hh:mi:ss'),1,2) + 12) * 3600 + 
					 substr(to_char(TIME_LOG,'hh:mi:ss'),4,2) * 60 +
					 substr(to_char(TIME_LOG,'hh:mi:ss'),7,2)))
					 else ((substr(to_char(TIME_LOG,'hh:mi:ss'),1,2) * 3600 + 
					 substr(to_char(TIME_LOG,'hh:mi:ss'),4,2) * 60 +
					 substr(to_char(TIME_LOG,'hh:mi:ss'),7,2))) end as DD
					 FROM password_expired where hr_id = '".$row_history[$i]['HR_ID']."'";
			$row1 = $this->db->query($sql1);
			$first_date = $row1['DD'];
			$sql2 = "SELECT CASE upper(substr(to_char(systimestamp,'hh:mi:ssam'),9,2)) when 'PM'
					 THEN (((substr(to_char(systimestamp,'hh:mi:ss'),1,2) + 12) * 3600 + 
					substr(to_char(systimestamp,'hh:mi:ss'),4,2) * 60 +
					substr(to_char(systimestamp,'hh:mi:ss'),7,2)))
					else ((substr(to_char(systimestamp,'hh:mi:ss'),1,2) * 3600 + 
					substr(to_char(systimestamp,'hh:mi:ss'),4,2) * 60 +
					substr(to_char(systimestamp,'hh:mi:ss'),7,2))) end as DD
					FROM dual ";
			$row2 = $this->db->query($sql2);
			$second_date = $row2['DD'];
			$result = $second_date - $first_date ;
			if($result >= $LOG_TIME)
			{
				$sql = "update logins set active_flag = 0 where hr_id = '".$row_history[$i]['HR_ID']."'";
				$result = $this->db->query($sql);
				//OCICommit($odb->db_connect_id);
				$sql = "update password_expired set time_log = null where hr_id = '".$row_history[$i]['HR_ID']."'";
				$result = $this->db->query($sql);
				//OCICommit($odb->db_connect_id);
			}
		}
	}
}